package com.googlecode.cswish.oosearch;

import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import javax.annotation.Resource;
import javax.persistence.DiscriminatorColumn;
import javax.persistence.Transient;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.time.DateUtils;
import org.apache.commons.lang3.ClassUtils;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Service;

import com.googlecode.cswish.annotation.GenericType;
import com.googlecode.cswish.annotation.QLFormula;
import com.googlecode.cswish.model.ModelInfo;
import com.googlecode.cswish.model.ParameterMap;
import com.googlecode.cswish.model.PropertyInfo;
import com.googlecode.cswish.model.Saas;
import com.googlecode.cswish.struts.ContextHolder;
import com.googlecode.cswish.struts.hibernate.IDynamicModel;
import com.googlecode.cswish.struts.hibernate.ISite;
import com.googlecode.cswish.struts.spring.BeanIntrospector;
import com.googlecode.cswish.util.ClassUtil;
import com.googlecode.cswish.util.OgnlExUtil;
import com.googlecode.cswish.util.StringUtil;
import com.googlecode.cswish.util.bean.ModelScanner;
import com.googlecode.cswish.util.bean.ModelScanner.ScannerCallback;
import com.googlecode.cswish.util.bean.ModelScanner.ScannerFlag;
import com.googlecode.cswish.util.bean.ModelUtil;
import com.googlecode.cswish.util.bean.ScannerContext;


/**
 * Parse a model object to Query Language (QL), the compare flag contains >,<,!,=,%,^ (see getCmpFlag)
 * 
 * @author Jerry.Feng Date: 2005-2-28 9:41:51
 * @version $id: $
 */
@Service
public class QLParse {
	/**
	 * Logger for this class
	 */
	private static final Logger logger = Logger.getLogger(QLParse.class);
	
	@Resource
	private BeanIntrospector beanIntrospector;
	
	@Resource
	private OgnlExUtil ognlExUtil;
	
	private static final String SELECT = "select ";
	private static final String FROM = " from ";
	private static final String AS = " as ";

	private static final String WHERE = " where ";
	public static final String DEFAULT_ALIAS = "a";

//	private static final int DIALECT = 1;	// 0 -- oracle, 1 -- sql server

	public QLParse() {
	}

	public QLInfo getSelectHQL(Object model, Condition condition) {
		QLInfo hqlInfo = getFromClause(model, condition);
		toSelectHQL(hqlInfo);
		return hqlInfo;
	}
	
	public QLInfo toSelectHQL(QLInfo hqlInfo) {
		hqlInfo.setQl(SELECT + DEFAULT_ALIAS + hqlInfo.getQl());
		return hqlInfo;
	}
	
	// TODO: optimize the design
	public void setSort(QLInfo qlInfo, String modelName, Object model, String sort) {
		if (sort != null && sort.length() > 0 && sort.charAt(0) == ':') {
			int index = sort.indexOf('.', 1);
			if (index > 0) {
				int end = sort.indexOf('(', index);
				if (end > 0) {
					sort = sort.substring(index + 1, end);
				} else {	
					sort = sort.substring(index + 1);
				}
			}
		}
		
		String formatSort;
		if (StringUtils.isEmpty(sort) || sort.startsWith("a.")) {
			// don't format the standard sort field
			formatSort = sort;
		} else {
			try {
				StringBuilder from = new StringBuilder();
				StringBuilder realSort = new StringBuilder(sort.length() + 5);
				
				// from clause ==> split "inner join" ==> indexOf ' ' ==> put(key, value)
				QLClause qlClause = parse(qlInfo.getQl());
				String ql = removeInvalidJoinClause(qlClause);		// TODO: necessary?
				qlInfo.setQl(ql);
				
				Map<String, String> aliasMapping = qlClause.getFromTables();
				if (sort.indexOf(',') == -1) {
					formatSort = formatSort(from, realSort, aliasMapping, qlInfo, modelName, model, sort);
				} else {
					String[] sortFields = StringUtils.split(sort, '.');
					StringBuilder sb = new StringBuilder();
					for (int i = 0, len = sortFields.length - 1; i < len; i++) {
						String theFormattedSort = formatSort(from, realSort, aliasMapping, qlInfo, modelName, model, sort);
						if (sb.length() > 0) {
							sb.append(',');
						}
						sb.append(theFormattedSort);
					}
					formatSort = sb.toString();
				}
			} catch (Exception ex) {
				if (logger.isDebugEnabled()) {
					logger.debug("Failed to find sort property:" + sort + ", ignore it", ex);
				}
				formatSort = null;
			}
		}
		qlInfo.setCompiledSort(formatSort);
	}
	
	private String removeInvalidJoinClause(QLClause qlClause) {
		String fromClause = qlClause.getFromClause();
		String whereClause = qlClause.getWhereClause();
		for (Entry<String, String> tableMapping : new ArrayList<Entry<String, String>>(
				qlClause.getFromTables().entrySet())) {
			String tablePrefix = tableMapping.getValue() + '.';
			if (whereClause.indexOf(tablePrefix) == -1) {
				String identify = " left join " + tableMapping.getKey() + ' ' + tableMapping.getValue();
				String newFromClause = StringUtils.replace(fromClause, identify, "");
				if (newFromClause != fromClause) {
					fromClause = newFromClause;
					qlClause.getFromTables().remove(tableMapping.getKey());
				}
			}
		}
		if (whereClause.length() > 0) {
			return " from " + fromClause + " where " + whereClause;
		} else {
			return " from " + fromClause;
		}
	}

	private String formatSort(StringBuilder from, StringBuilder realSort, Map<String, String> aliasMapping
			, QLInfo qlInfo, String modelName, Object model, String sort) {
		String formatSort;
		Class toType = parseLeftJoinProperty(from, realSort, DEFAULT_ALIAS, aliasMapping, modelName, model, sort);
		if (toType != null) {
			if (from.length() > 0) {
				String sfrom = from.toString();
				if (qlInfo.getQl().indexOf(sfrom) == -1) {
					int whereIndex = qlInfo.getQl().indexOf(" where ");
					if (whereIndex == -1) {
						qlInfo.setQl(qlInfo.getQl() + sfrom);
					} else {
						qlInfo.setQl(qlInfo.getQl().substring(0, whereIndex) + sfrom + qlInfo.getQl().substring(whereIndex));
					}
				}
			}
			formatSort = realSort.toString();
		} else {
			if (logger.isDebugEnabled()) {
				logger.debug("Failed to find sort property:" + sort + ", ignore it");
			}
			formatSort = null;
		}
		return formatSort;
	}
	
	/**
	 * NOTICE: Don't populate the default model mapping 
	 * 
	 * @param ql
	 * @return
	 */
	public static QLClause parse(String ql) {
		int begin = ql.indexOf(" from ");
		int end = ql.indexOf(" where ", begin);
		String whereClause;
		if (end == -1) {
			end = ql.length();
			whereClause = "";
		} else {
			whereClause = ql.substring(end + 7);
		}
		
		String fromClause = ql.substring(begin + 6, end);
		String[] joins = StringUtils.splitByWholeSeparator(fromClause, " join ");		// FIXME:
		Map<String, String> aliasMapping;
		if (joins.length == 1) {
			aliasMapping = new HashMap<String, String>();
		} else {
			aliasMapping = new HashMap<String, String>();
			for (int i = 1; i < joins.length; i++) {
				String str = joins[i].trim();
				int keyEnd = str.indexOf(' ');
				String key = str.substring(0, keyEnd);
				int aliasBegin = keyEnd + 1;
				int aliasEnd = str.indexOf(' ', aliasBegin + 1);
				if (aliasEnd == -1) {
					aliasEnd = str.length();
				}
				String alias = str.substring(aliasBegin, aliasEnd);
				aliasMapping.put(key, alias);
			}
		}
		
		return new QLClause(fromClause, whereClause, aliasMapping);
	}

	/**
	 * Build where clause according to the input parameters, NOTICE: don't support innerjoin at present
	 * 
	 * @param modelName
	 * @param searchModel
	 * @return
	 */
	public QLInfo getWhereClause(String modelName, ParameterMap searchModel, Condition condition) {
		Map<String, Object> grouped = groupModel(modelName, searchModel);
		return getWhereClause(DEFAULT_ALIAS, grouped, condition);
	}
	
	// group parameter and convert string[] to string
	private Map<String, Object> groupModel(String modelName, ParameterMap searchModel) {
		Map<String, Object> grouped = new HashMap<String, Object>();
		grouped.put(IDynamicModel.ENTITY_NAME, modelName);
		
		for (Entry<String, Object> entry : searchModel.entrySet()) {
			String property = entry.getKey();
			if (entry.getValue() instanceof ParameterMap) {
				// skip it, all the property should exist in the root ParameterMap
				continue;
			}
			
			int end = property.indexOf('.');
			boolean isNestedProperty = end > 0;
			Map<String, Object> target = grouped;
			String targetProperty;
			if (isNestedProperty) {
				int begin = 0;
				ModelInfo modelInfo = beanIntrospector.getModelInfo(modelName);
				while (end > 0) {
					String simpleProperty = property.substring(begin, end);
					PropertyInfo propertyInfo = modelInfo.getProperty(simpleProperty);
					if (propertyInfo == null) {
						// found the invalid property
						end = -1;
						begin = -1;
					} else {
						Map<String, Object> subPropertyModel = (Map<String, Object>) target.get(simpleProperty);
						if (subPropertyModel == null) {
							subPropertyModel = new HashMap<String, Object>();
							subPropertyModel.put(IDynamicModel.ENTITY_NAME, propertyInfo.getGenericType());
							target.put(simpleProperty, subPropertyModel);
						}
						
						modelInfo = beanIntrospector.getModelInfo(propertyInfo.getGenericType());
						target = subPropertyModel;
						begin = end + 1;
						end = property.indexOf('.', begin);
					}
				}
				if (begin == -1) {
					targetProperty = null;
				} else {
					targetProperty = property.substring(begin);
				}
			} else {
				targetProperty = property;
			}
			
			if (targetProperty != null) {
				Object val = entry.getValue();
				if (val instanceof String[]) {
					String[] strs = (String[]) val;
					if (strs.length > 0) {
						target.put(targetProperty, strs[0]);
					}
				} else {
					target.put(targetProperty, val);
				}
			}
		}
		return grouped;
	}
	
	
	public QLInfo getWhereClause(String tableAlias, Map<String, Object> searchModel, Condition condition) {
		StringBuilder where = new StringBuilder();
		List<Object> params = new ArrayList<Object>();
		
		// check if there's id property
		Object id = searchModel.get("id");
		if (notEmpty(id)) {
			String cmpFlag = getCmpFlag(id, Integer.class.getName(), condition.getCompareFlag());
			if (Condition.COMPARE_EQUAL.equals(cmpFlag)) {
				Object cmpVal = getCmpValue(id, Integer.class.getName(), Condition.COMPARE_EQUAL, condition.getCompareFlag());
				if (cmpVal != null) {
					where.append(tableAlias).append(".id=?");
					params.add(cmpVal);
					return new QLInfo(where.toString(), params);
				}
			}
		}
		
		// scan the normal properties
		String className = (String) searchModel.get(IDynamicModel.ENTITY_NAME);
		ModelInfo modelInfo = beanIntrospector.getModelInfo(className);
		for (Entry<String, Object> entry : searchModel.entrySet()) {
			Object value = entry.getValue();
			String property = entry.getKey();
			if (notEmpty(value) && !property.equals(IDynamicModel.ENTITY_NAME)) {
				
				if (ISite.DEFAULT_ID_PROPERTY.equals(property)){
					if (where.length() > 0) {
						where.append(condition.getLogicFlag());
					}
					appendCondition(tableAlias, where, params, property, "=", value);
					continue;
				}
				
				// 1. get the real property and type
				PropertyInfo propertyInfo = modelInfo.getProperty(property);
				if (propertyInfo == null) {
					if ("class".equals(property)) {
						// FIXME: get the cmp type according to @DiscriminatorColumn.discriminatorType
						String cmpFlag = getCmpFlag(value, Integer.class.getName(), Condition.COMPARE_EQUAL);
						Object cmpVal = getCmpValue(value, Integer.class.getName(), cmpFlag, Condition.COMPARE_EQUAL);
						appendCondition(tableAlias, where, params, property, cmpFlag, cmpVal);
					}
					continue;		// skip the invalid property
				}
				
				Method readMethod = propertyInfo.getReadMethod();
				QLFormula qlFormula = null;
				String persistType;
				if (readMethod.getAnnotation(Transient.class) != null) {
					qlFormula = readMethod.getAnnotation(QLFormula.class);
					if (qlFormula != null) {
						// TODO: how to process if the property contains nested collection property?
						property = getQLFormulaValue(modelInfo, propertyInfo, qlFormula, searchModel);
						
						if (qlFormula.isColumn()) {
							String firstProperty;
							int dotIndex = property.indexOf('.');
							if (dotIndex == -1) {
								firstProperty = property;
							} else {
								firstProperty = property.substring(0, dotIndex);
							}
							if (searchModel.get(firstProperty) != null) {
								// it's duplicated value, don't process the QLFormula at this time
								persistType = null;
							} else {
								PropertyInfo info = beanIntrospector.getPropertyInfo(className, property);
								if (info != null) {
									persistType = info.getType();
								} else {
									persistType = qlFormula.type().getName();
								}
							}
						} else {
							persistType = null;
							if (where.length() > 0) {
								where.append(condition.getLogicFlag());
							}
							String replacement = tableAlias + '.';
							if (!replacement.equals("a.")) {
								where.append(StringUtils.replace(property, "a.", replacement));
							} else {
								where.append(property);
							}
						}
					} else {
						persistType = null;
					}
				} else {
					persistType = propertyInfo.getType();
				}
				
				// 2. parse the value
				if (persistType != null) {
					Class persistTypeClass = null;
					try {
						persistTypeClass = Class.forName(persistType);
					} catch (ClassNotFoundException e) {
						logger.error("Can't parse class: " + persistType, e);
					}
					// case 1: both persistType & value is Collection. 
					// case 2: persistType is Collection, but value is Object
					if (persistTypeClass != null && Collection.class.isAssignableFrom(persistTypeClass) || value instanceof Collection) {
						String nextAlias = nextTableAlias(tableAlias);
						if (value instanceof Collection) {
							Collection<Map> coll = (Collection<Map>)value;
							value = coll.iterator().next();
						}
						// FIXME: check & filter the sub-class value here?
						QLInfo qlInfo = getWhereClause(nextAlias, (Map)value, condition);
						if (!qlInfo.getQl().isEmpty()) {
							if (where.length() > 0) {
								where.append(condition.getLogicFlag());
							}
							
							where.append("exists (from ").append(tableAlias).append(".").append(property).append(" ").append(nextAlias).append(" where (");
							where.append(qlInfo.getQl());
							where.append("))");
							params.addAll(qlInfo.getParams());
						}
					} else if (value instanceof Map) {
						QLInfo qlInfo = getWhereClause(tableAlias + '.' + property, (Map<String, Object>)value, condition);
						if (!qlInfo.getQl().isEmpty()) {
							if (where.length() > 0) {
								where.append(condition.getLogicFlag());
							}
							
							where.append(qlInfo.getQl());
							params.addAll(qlInfo.getParams());
						}
					} else {
						// string expression supports complex logic
						if (value instanceof String) {
							// >xxx&<xxx, >xxx|<xxx
							String strVal = (String) value;
							int index = -1;
							if (condition.isEnableComplexSearch() && (index = strVal.indexOf('&')) >= 0 && (index == 0 || strVal.charAt(index - 1) != '\\')) {	// and
								if (where.length() > 0) {
									where.append(condition.getLogicFlag());
								}
						
								String val1 = strVal.substring(0, index);
								String val2 = strVal.substring(index + 1);
								String cmpFlag = getCmpFlag(val1, persistType, condition.getCompareFlag());
								Object cmpVal = getCmpValue(val1, persistType, cmpFlag, condition.getCompareFlag());
								appendCondition(tableAlias, where, params, property, cmpFlag, cmpVal);
								where.append(Condition.LOGIC_AND);
								cmpFlag = getCmpFlag(val2, persistType, condition.getCompareFlag());
								cmpVal = getCmpValue(val2, persistType, cmpFlag, condition.getCompareFlag());
								appendCondition(tableAlias, where, params, property, cmpFlag, cmpVal);
							} else if (condition.isEnableComplexSearch() && (index = strVal.indexOf('|')) >= 0 && (index == 0 || strVal.charAt(index - 1) != '\\')) {	// or
								if (where.length() > 0) {
									if (index == 0) {
										where.append(Condition.LOGIC_OR);			// the whole logic is or
									} else {
										where.append(condition.getLogicFlag());
									}
								}
								
								String val1 = strVal.substring(0, index);
								String val2 = strVal.substring(index + 1);
								where.append('(');
								if (val1.length() > 0) {
									String cmpFlag = getCmpFlag(val1, persistType, condition.getCompareFlag());
									val1 = StringUtil.removeEscapeChar(val1);
									Object cmpVal = getCmpValue(val1, persistType, cmpFlag, condition.getCompareFlag());
									appendCondition(tableAlias, where, params, property, cmpFlag, cmpVal);
									where.append(Condition.LOGIC_OR);
								}
								
								String cmpFlag = getCmpFlag(val2, persistType, condition.getCompareFlag());
								val2 = StringUtil.removeEscapeChar(val2);
								Object cmpVal = getCmpValue(val2, persistType, cmpFlag, condition.getCompareFlag());
								appendCondition(tableAlias, where, params, property, cmpFlag, cmpVal);
								where.append(')');
							} else {
								String cmpFlag = getCmpFlag(value, persistType, condition.getCompareFlag());
								if (condition.isEnableComplexSearch()) {
									// escape the value
									value = StringUtil.removeEscapeChar(strVal);
								}
								Object cmpVal = getCmpValue(value, persistType, cmpFlag, condition.getCompareFlag());
								if (cmpVal != null) {
									if (where.length() > 0) {
										where.append(condition.getLogicFlag());
									}
									
									appendCondition(tableAlias, where, params, property, cmpFlag, cmpVal);
								}
							}
						} else {
							String cmpFlag = getCmpFlag(value, persistType, condition.getCompareFlag());
							Object cmpVal = getCmpValue(value, persistType, cmpFlag, condition.getCompareFlag());
							if (cmpVal != null) {
								if (where.length() > 0) {
									where.append(condition.getLogicFlag());
								}
								
								appendCondition(tableAlias, where, params, property, cmpFlag, cmpVal);
							}
						}
					}
				}
			}
		}
		
		QLInfo qlInfo = new QLInfo(where.toString(), params);
		return qlInfo;
	}

	public String getQLFormulaValue(ModelInfo modelInfo,
			PropertyInfo propertyInfo, QLFormula qlFormula, Object model) {
		String property;
		property = qlFormula.value();
		if (property.charAt(0) == ':') {
			try {				
				Method method = null;
				Class clazz = modelInfo.getTypeClass();
				String methodName = property.substring(1);
				try {
					method = clazz.getMethod(methodName);
				} catch (Exception ex) {
					method = clazz.getMethod(methodName, Map.class);
				}
				if (method.getParameterTypes().length == 0) {
					property = (String)method.invoke(null);
				} else {
					if (!(model instanceof Map)) {
						// try to convert it to the real model
						model = toSearchModel(model, null);
					}
					property = (String)method.invoke(null, model);
				}
			} catch (Exception ex) {
				logger.error("Wrong QLFormula definition, " + modelInfo.getType() + '#' + propertyInfo.getName(), ex);
			}
		}
		return property;
	}

	private void appendCondition(String tableAlias, StringBuilder where, List<Object> params, String property, String cmpFlag,
			Object cmpVal) {
		if (Character.isLetter(property.charAt(0))) {
			where.append(tableAlias).append(".");
		}
		where.append(property);
		where.append(cmpFlag).append('?');
		params.add(cmpVal);
	}
	
	private Class parseLeftJoinProperty(StringBuilder from, StringBuilder realPropertyName, String tableAlias,
			Map<String, String> aliasMapping, String modelName, Object model, String propertyName) {
		ModelInfo modelInfo = beanIntrospector.getModelInfo(modelName);
		PropertyInfo propertyInfo = modelInfo.getProperty(propertyName);
		
		// get the nested property
		Class toType = null;
		if (propertyInfo == null) {
			int index = propertyName.indexOf('.');
			if (index == -1) {
				return null;			// it's invalid name
			}
			
			int lastIndex = 0;
			while (index > 0) {
				String property = propertyName.substring(lastIndex, index);
				propertyInfo = modelInfo.getProperty(property);
				if (propertyInfo == null)  {
					break;
				}
				
				Method readMethod = propertyInfo.getReadMethod();
				QLFormula qlFormula = null;
				if (readMethod.getAnnotation(Transient.class) != null) {
					qlFormula = readMethod.getAnnotation(QLFormula.class);
					if (qlFormula != null) {
						String formulaValue = getQLFormulaValue(modelInfo, propertyInfo, qlFormula, model);
						toType = parseLeftJoinProperty(from, realPropertyName, tableAlias, aliasMapping, modelName, model, formulaValue);
					} else {
						toType = null;
					}
				} else {
					toType = propertyInfo.getGenericTypeClass();
					String joinPropertyName = tableAlias + '.' + property;
					String newAlias = aliasMapping.get(joinPropertyName);
					if (newAlias == null) {
						newAlias = nextTableAlias(aliasMapping.values());
						from.append(" left join ").append(joinPropertyName).append(' ').append(newAlias);
						aliasMapping.put(joinPropertyName, newAlias);
					}
					tableAlias = newAlias;
				}
				
				if (toType != null) {
					lastIndex = index + 1;
					index = propertyName.indexOf('.', lastIndex);
					modelName = toType.getName();
					modelInfo = beanIntrospector.getModelInfo(modelName);
				} else {
					index = -1;
				}
			}
			
			propertyName = propertyName.substring(lastIndex);
			propertyInfo = modelInfo.getProperty(propertyName);
		}
		
		if (propertyInfo != null) {
			Method readMethod = propertyInfo.getReadMethod();
			QLFormula qlFormula = null;
			if (readMethod.getAnnotation(Transient.class) != null) {		// ignore the transient field except if defined the QLFormula expression
				qlFormula = readMethod.getAnnotation(QLFormula.class);
				if (qlFormula != null) {
					String formulaValue = getQLFormulaValue(modelInfo, propertyInfo, qlFormula, model);
					toType = parseLeftJoinProperty(from, realPropertyName, tableAlias, aliasMapping, modelName, model, formulaValue);
				}
			} else {
				toType = propertyInfo.getGenericTypeClass();
				realPropertyName.append(tableAlias).append('.').append(propertyName);
			}
		}
		return toType;
	}
	
	private Date getTodayMax(Date today) {
		Calendar todayCal = Calendar.getInstance();
		todayCal.setTime(today);
		todayCal.setLenient(false);
		todayCal.set(Calendar.HOUR_OF_DAY, 23);
		todayCal.set(Calendar.MINUTE, 59);
		todayCal.set(Calendar.SECOND, 59);
		todayCal.set(Calendar.MILLISECOND, 999);
		return todayCal.getTime();
	}
	
	private Date getTodayMin(Date today) {
		if (today == null) {
			return null;
		}
		return DateUtils.truncate(today, Calendar.DATE);
	}

	public Map<String, Object> toSearchModel(Object model, StringBuilder sbModelName) {
		String modelName;
		Map<String, Object> searchModel;
    	if (model instanceof ParameterMap) {
    		ParameterMap parameterMap = (ParameterMap) model;
    		
    		modelName = parameterMap.getModelName();
    		if (modelName == null) {
				modelName = ClassUtil.getRuntimeType(GenericType.MODEL);
				parameterMap.setModelName(modelName);
			}
    		Class clazzModel = ClassUtil.getClass(modelName);
    		if (Saas.class.isAssignableFrom(clazzModel)){
    			Integer sid = ContextHolder.get().getSid();
    			if (sid != null){
    				parameterMap.put(ISite.DEFAULT_ID_PROPERTY, sid);
    			}
    		}
    		searchModel = groupModel(modelName, parameterMap);
    	} else if (model instanceof Map) {
    		searchModel = (Map<String, Object>) model;
    		modelName = (String)searchModel.get(IDynamicModel.ENTITY_NAME);
    	} else {
    		searchModel = new HashMap<String, Object>();
    		modelName = ClassUtil.getClassName(model);
    		searchModel.put(IDynamicModel.ENTITY_NAME, modelName);
    		
    		Object id = ModelUtil.getSimpleProperty(model, "id");
    		if (id != null && id instanceof Number) {
    			searchModel.put("id", id);
    		} else {
        		if (model instanceof Saas){
        			Integer sid = ContextHolder.get().getSid();
        			if (sid != null){
        				searchModel.put(ISite.DEFAULT_ID_PROPERTY, sid);
        			}
        		}
        		
	    		ModelScanner.getInstance().scan(model, searchModel, new ScannerCallback() {
					@SuppressWarnings({ "rawtypes", "unchecked" })
					@Override
					public ScannerFlag proceed(ScannerContext scannerContext) {
						Object value = scannerContext.getSrcPropertyValue();
						if (value == null) {
							return ScannerFlag.SCANSIBLING;
						}
						
						Map<String, Object> destModel = (Map<String, Object>) scannerContext.getDestModel();
						String name = scannerContext.getPropertyDescriptor().getName();
						if (scannerContext.isSimpleObj()) {
							destModel.put(name, value);
	    					return ScannerFlag.SCANSIBLING;
	    				} else if (scannerContext.isCollection()) {
	    					Collection coll = (Collection)value;
	    					List destColl = new ArrayList();
	    					boolean allChildrenHasId = true;
	    					for (Object src : coll) {
	    						Map destMap = new HashMap();
	    						destMap.put(IDynamicModel.ENTITY_NAME, ClassUtil.getClassName(src));
	    						destColl.add(destMap);
	    						
	    						Object id = ModelUtil.getSimpleProperty(src, "id");
	    						if (id != null && id instanceof Number) {
		    						destMap.put("id", id);
	    						} else {
	    							allChildrenHasId = false;
	    						}
	    					}
	    					destModel.put(name, destColl);
	    					scannerContext.setDestPropertyValueWithoutModify(destColl);
	    					return allChildrenHasId ? ScannerFlag.SCANSIBLING : ScannerFlag.SCANCHILD;
	    				} else {
	    					Map<String, Object> destMap = new HashMap<String, Object>();
	    					destMap.put(IDynamicModel.ENTITY_NAME, scannerContext.getPropertyDescriptor().getType());
	    					destModel.put(name, destMap);
	    					scannerContext.setDestPropertyValueWithoutModify(destMap);
	    					
	    					Object id = ModelUtil.getSimpleProperty(scannerContext.getSrcPropertyValue(), "id");
	    					if (id != null && id instanceof Number) {
	    						destMap.put("id", id);
	    						return ScannerFlag.SCANSIBLING;
	    					} else {
	    						return ScannerFlag.SCANCHILD;
	    					}
	    				}
					}
				});// scan
    		}
    	}
    	
    	if (sbModelName != null) {
    		sbModelName.append(modelName);
    	}
    	return searchModel;
	}
	
    public QLInfo getFromClause(Object model, Condition condition) {
    	StringBuilder sbModelName = new StringBuilder();
    	Map<String, Object> searchModel = toSearchModel(model, sbModelName);
    	
    	// build sql according to advance model
		StringBuilder from = new StringBuilder();
		from.append(" from ").append(sbModelName).append(" a");
		QLInfo qlInfo = getWhereClause(DEFAULT_ALIAS, searchModel, condition);
		String ql = qlInfo.getQl();
		if (ql.length() > 0) {
			ql = from.append(" where ").append(ql).toString();
		} else {
			ql = from.toString();
		}
		qlInfo.setQl(ql);

    	// Set page information 
    	qlInfo.setFirstResult(condition.getOffset());
    	qlInfo.setMaxResults(condition.getSize());
    	
    	// Set sort & order
    	if (condition.getSort() != null) {
    		this.setSort(qlInfo, sbModelName.toString(), searchModel, condition.getSort());
    		qlInfo.setDir(condition.getDir());
    	}
    	return qlInfo;
    }
    
    public static String nextTableAlias(Collection<String> tableAlias) {
    	String max = tableAlias.isEmpty() ? DEFAULT_ALIAS : tableAlias.iterator().next();
    	for (String name : tableAlias) {
    		if (name.compareTo(max) > 0) {
    			max = name;
    		}
    	}
    	return nextTableAlias(max);
    }
    
    private static String nextTableAlias(String tableAlias) {
    	char alias = tableAlias.length() == 0 ? 'a' : tableAlias.charAt(0);
    	return String.valueOf((char)((int)alias + 1));
    }
    
    private boolean notEmpty(Object value) {
    	boolean hasValue = false;
    	
    	if (value != null) {
    		Class type = value.getClass();
    		if (type != null && type.isPrimitive()) {
        		if (boolean.class == type) {
        			hasValue = ((Boolean)value).booleanValue();
        		} else {
        			int i = Integer.parseInt(value.toString());
        			hasValue = (i != 0);
        		}
        	} else {
	    		if (value instanceof Collection) {
	    			try {
		    			if (((Collection)value).size() > 0) {//avoid the hibernate lazy load exception
		    				hasValue = true;
		    			}
	    			} catch (Throwable t) {
	    			}
	    		} else if (value instanceof String) {
	    			hasValue = ((String) value).length() > 0;
	    		} else if (value instanceof Map) {
	    			hasValue = ((Map)value).size() > 1;		// skip IDynamicModel.ENTITY_NAME
	    		} else {
	    			hasValue = true;
	    		}
        	}
    	}
		return hasValue;
	}

    private String getCmpFlag(Object value, String toType, String defaultCmpFlag) {
    	String realCmpFlag = null;
    	if (value instanceof String) {
    		String strValue = (String)value;
    		
    		if (strValue.length() > 0) {
	    		// 1. try to use the advanced condition
		    	char flag = strValue.charAt(0);
		    	// see Condition.FLAG_XXX
				if (flag == '>' || flag == '<' || flag == '!' || flag == '=' || flag == '%' || flag == '^') {
					if (flag == '>') {
						realCmpFlag = ">=";
					} else if (flag == '<') {
						realCmpFlag = "<=";
					} else if (flag == '=') {
						realCmpFlag = "=";
					} else if (flag == '^' || flag == '%') {
						realCmpFlag = " like ";
					} else {
						// for string, uses 'like', the others use '='
						if (toType.equals(String.class.getName())) {
							if (flag == '!') {
								realCmpFlag = Condition.COMPARE_NOT_LIKE;	
							} else {
								realCmpFlag = Condition.COMPARE_LIKE;
							}
						} else {
							if (flag == '!') {
								realCmpFlag = "<>";	
							} else {
								realCmpFlag = "=";
							}
						}
					}
				}// if flag
			}// if length
    	}

    	if (realCmpFlag == null) {
	    	if (toType.equals(String.class.getName())) {
	    		// 2. use default condition for the String type
				if (Condition.COMPARE_AUTO_LIKEFIRST.equals(defaultCmpFlag)) {
		    		realCmpFlag = Condition.COMPARE_LIKE;
				} else if (Condition.COMPARE_AUTO_EQFIRST.equals(defaultCmpFlag)) {
					realCmpFlag = Condition.COMPARE_EQUAL;
				} else {
					realCmpFlag = defaultCmpFlag;
				}
	    	} else {
	    		// 3. use default condition for the other type
	    		if (Condition.COMPARE_AUTO_LIKEFIRST.equals(defaultCmpFlag) || Condition.COMPARE_AUTO_EQFIRST.equals(defaultCmpFlag)) {
	    			realCmpFlag = Condition.COMPARE_EQUAL;
	        	} else {
	        		realCmpFlag = defaultCmpFlag;
	        	}
	    	}
    	}
    	return realCmpFlag;
    }
    
    @SuppressWarnings({ "rawtypes", "unchecked" })
	private Object getCmpValue(Object value, String toType, String cmpFlag, String defaultFlag) {
    	char valueFlag = '\0';
		if (value instanceof String) {
			String strValue = (String)value;
			if (strValue.length() > 0) {
				valueFlag = strValue.charAt(0);
				if (valueFlag == '>' || valueFlag == '<' || valueFlag == '!' || valueFlag == '='
						|| valueFlag == '^' || valueFlag == '%') {
					value = strValue.substring(1);		// skip the first compare flag
				}
			}
		}
		
		if (Condition.COMPARE_LIKE.equals(cmpFlag)) {
			if (valueFlag == '^') {
				value = value + "%";
			} else {
				value = "%" + value + "%";
			}
		} else {
			Class toTypeClass = ClassUtil.forName(toType);
			if (!toTypeClass.isAssignableFrom(value.getClass())) {    			
				value = ognlExUtil.convertValue(value, toTypeClass, null);
			}
	    	if (Date.class.isAssignableFrom(toTypeClass)) {
	    		if (cmpFlag.charAt(0) == '>') {
	    			value = getTodayMin((Date) value);
				} else if (cmpFlag.charAt(0) == '<') {
					value = getTodayMax((Date) value);
				} else {
					// keep the original value?
				}
			}
    	}
    	return value;
    }
    
    public QLInfo getSQLWhereClause(String tableAlias, Map<String, Object> searchModel, Condition condition) {
    	// TODO: it's a simple implement only support simple model
    	String modelName = (String)searchModel.get(IDynamicModel.ENTITY_NAME);
    	ModelInfo modelInfo = beanIntrospector.getModelInfo(modelName);
		
    	StringBuilder ql = new StringBuilder();
    	List<Object> params = new ArrayList<Object>();
    	List<String> paramNames = new ArrayList<String>();
		for (Entry<String, Object> entry : searchModel.entrySet()) {
			PropertyInfo propInfo = modelInfo.getProperty(entry.getKey());
			if (propInfo != null) {
				String columnName = propInfo.getColumnName() == null ?
						propInfo.getName() : propInfo.getColumnName();
				if (ql.length() > 0) {
					ql.append(condition.getLogicFlag());
				}
				ql.append(tableAlias).append(".").append(columnName); 
				String cmpFlag = getCmpFlag(entry.getValue(), propInfo.getType(), condition.getCompareFlag());
				ql.append(cmpFlag);
				ql.append(':').append(propInfo.getName());
				paramNames.add(propInfo.getName());
				
				Object cmpValue = getCmpValue(entry.getValue(), propInfo.getType(), cmpFlag, condition.getCompareFlag());
				params.add(cmpValue);
			}
		}
		QLInfo qlInfo = new QLInfo(ql.toString(), params);
		qlInfo.setParamNames(paramNames);
		return qlInfo;
    }
}
